从复杂类型选择数据

本章描述如何查询复杂数据类型。复杂数据类型是使用 SQL 类型构造函数从其他数据类型的组合构建的。SQL 语句可以访问复杂数据类型中的个别组件。复杂数据类型是行类型或集合类型。

ROW 类型具有组合一个或多个相关数据字段的实例。这两种 ROW 类型是已命名和未命名。

集合类型具有这样的实例：在其中，每种集合值包含具有相同数据类型的一组元素，这些数据类型可以是任何基本或复杂数据类型。集合可以由 LIST 、SET 或 MULTISET 数据类型组成。

重要

对于复杂数据类型没有跨数据库的支持。只能在本地数据库中对它们进行操作。

有关数据库服务器支持的数据类型的更完整描述，请参阅《GBase 8s SQL 参考指南》中的数据类型一章。

有关如何创建使用复杂类型的信息，请参阅《GBase 8s SQL 参考指南》和《GBase 8s SQL 指南：语法》。

选择行类型数据

本节描述如何查询定义为行类型的数据。ROW 类型是一个复杂类型，包含一个或多个相关数据字段。

两种 ROW 类型如下：

已命名 ROW 类型

已命名的 ROW 类型可以定义表、列、其它行类型列的字段、程序变量、语句变量以及例程返回值。

未命名 ROW 类型

未命名 ROW 类型可以定义列、其它行类型列的字段、程序变量、语句局部变量、例程返回值和常量。

本节中使用的示例使用已命名 ROW 类型 zip_t 、address_t 和 employee_t，这些 ROW 类型定义 employee 表。下图显示创建 ROW 类型和表的 SQL 语法。

图: 创建 ROW 类型和表的 SQL 语法

CREATE ROW TYPE zip_t
(
z_code    CHAR(5),
z_suffix  CHAR(4)
)

CREATE ROW TYPE address_t
(
street    VARCHAR(20),
city      VARCHAR(20),
state     CHAR(2),
zip       zip_t
)

CREATE ROW TYPE employee_t
(
name      VARCHAR(30),
address   address_t,
salary    INTEGER
)

CREATE TABLE employee OF TYPE employee_t

已命名的 ROW 类型 zip_t 、address_t 和 employee_t 充当类型表 employee 的字段和列的模板。类型表是在已命名 ROW 类型上定义的表。充当 employee 表的模板的 employee_t 类型将 address_t 类型用作 address 字段的数据类型。address_t 类型使用 zip_t 类型作为 zip 字段的数据类型。

下图显示了创建 student 表的 SQL 语法。student 表的 s_address 列定义为未命名的 ROW 类型。（s_address 列被定义为已命名的 ROW 类型。）

图: 创建 student 表的 SQL 语法

CREATE TABLE student
(
s_name      VARCHAR(30),
s_address   ROW(street VARCHAR (20), city VARCHAR(20),
state CHAR(2), zip VARCHAR(9)),
grade_point_avg DECIMAL(3,2)
)

选择类型表的列

对类型表的查询与对任何其他表的查询没有区别。例如：下列查询使用星号（*）来指定返回 employee 表所有列的 SELECT 语句。

图: 查询

SELECT * FROM employee

employee 表上的 SELECT 语句返回所有列的所有行。

图: 查询结果

name        Paul, J.
address     ROW(102 Ruby, Belmont, CA, 49932, 1000)
salary       78000

name        Davis, J.
address      ROW(133 First, San Jose, CA, 85744, 4900)
salary        75000
⋮

下列查询显示如何构造返回 employee 表的 name 和 address 列的行的查询。

图: 查询

SELECT name, address FROM employee

图: 查询结果

name        Paul, J.
address     ROW(102 Ruby, Belmont, CA, 49932, 1000)

name        Davis, J.
address     ROW(133 First, San Jose, CA, 85744, 4900)
⋮

选择包含行类型数据的列

行类型列是在已命名 ROW 类型或未命名 ROW 类型上定义的列。使用相同的 SQL 语法来查询已命名 ROW 类型和未命名行类型列。

对行类型列的查询返回 ROW 类型的所有字段的数据。字段是 ROW 类型中的组件数据类型。例如：employee 表的 address 列包含 street、city 、state 和 zip 字段。下列查询显示如何构造返回 address 列的所有字段的查询。

图: 查询

SELECT address FROM employee

图: 查询结果

address     ROW(102 Ruby, Belmont, CA, 49932, 1000)
address     ROW(133 First, San Jose, CA, 85744, 4900)
address     ROW(152 Topaz, Willits, CA, 69445, 1000))
⋮

要访问列包含的个别字段，使用单个点符号表示法来投影列的个别字段。例如：假设您要访问 employee 表的 address 列中的特定字段。以下 SELECT 语句投影 address 列的 city 和 state 字段。

图: 查询

SELECT address.city, address.state FROM employee

图: 查询结果

city             state

Belmont          CA
San Jose         CA
Willits          CA
⋮

用对已命名行类型列构造查询所用的方法来对未命名行类型列构造查询。例如：假设您想要访问图 2中 student 表的 s_address 列的数据。可以使用点符号表示法来查询对未命名行类型定义的列的个别字段。以下查询显示如何对 student 表构造返回 s_address 列的 city 和 state 字段的行的 SELECT 语句。

图: 查询

SELECT s_address.city, s_address.state FROM student

图: 查询结果

city             state

Belmont          CA
Mount Prospect   IL
Greeley          CO
⋮

字段投影

不要混淆列和字段。列只与表相关联，并且列投影将格式为 name_1.name2 的常规点符号表示法分别用于表和列。字段是 ROW 类型中的组件数据类型。使用 ROW 类型（和将 ROW 类型指定给单个列的能力），您可以使用形式为 name_a.name_b.name_c.name_d 的单点符号表示法来投影列的各个字段。GBase 8s 数据库服务器使用以下优先顺序规则来解释点符号表示法：

table_name_a . column_name_b . field_name_c . field_name_d
column_name_a . field_name_b . field_name_c . field_name_d

当特定标识的意义有歧义时。数据库服务器使用优先顺序规则来确定标识指定哪个数据库对象。考虑以下两个语句：

CREATE TABLE b (c ROW(d INTEGER, e CHAR(2)))
CREATE TABLE c (d INTEGER)

在下列 SELECT 语句中，表达式 c.d 引用表 c 的列 d（而不是 b 中列 c 的字段 d），原因是表标识具有比列标识更高的优先顺序：

SELECT * FROM b,c WHERE c.d = 10

为了避免引用错误的数据库对象，可为字段投影指定完全符号表示法。例如：假定您想要引用 b 中列 c 的字段 d（而不是表 c 的列 d）。以下语句指定想要引用的对象的表、列和字段标识：

SELECT * FROM b,c WHERE b.c.d = 10

重要

虽然优先顺序规则减少了数据库服务器误解字段投影的几率，但是建议对所有表、列和字段标识使用唯一名称。

使用字段投影来选择嵌套字段

行类型通常是列，但可将任何行类型表达式用于字段投影。当行类型表达式本身包含其它行类型时，表达式就包含嵌套字段。要访问表达式或个别字段中的嵌套字段，使用点符号表示法。要访问行类型的所有字段，使用星号（*）。本节描述行类型访问的两种方法。

有关如何将点符号表示法和星号符号表示法与行类型表达式配合使用的讨论，请参阅《GBase 8s SQL 指南：语法》中的表达式段。

选择行类型的个别字段

考虑 employee 表的 address 列，它包含字段 street 、city 、state 和 zip。此外，zip 字段包含嵌套字段：z_code 和 z_suffix。（您可能想要复查图 1对 zip 字段的查询返回 z_code 和 z_suffix 字段的行。但是，可以指定查询只返回特定嵌套字段。以下查询显示如何使用点符号表示法来构造只返回 address 列中 z_code 字段的行的 SELECT 语句。

图: 查询

SELECT address.zip.z_code FROM employee

图: 查询结果

z_code

39444
6500
76055
19004
⋮

使用星号表示法来访问行类型的所有字段

星号符号表示法仅在 SELECT 语句的选择列表中受支持。为投影列表中的行类型列指定列名时，数据库服务器返回列的所有字段的值。想要投影 ROW 类型内的所有字段时，还可以使用星号符号表示法。

下列查询使用星号符号表示法来返回 employee 表中 address 列的所有字段。

图: 查询

SELECT address.* FROM employee;

图: 查询结果

address   ROW(102 Ruby, Belmont, CA, 49932, 1000)
address   ROW(133 First, San Jose, CA, 85744, 4900)
address   ROW(152 Topaz, Willits, CA, 69445, 1000))
⋮

星号符号表示法使得执行某些 SQL 任务更容易。假设您创建返回行类型值的函数 new_row() 并且想要调用此函数并将返回的行插入到表中。数据库服务器没有提供处理此类操作的简便方法。但是，以下查询显示如何使用星号表示法来返回 new_row() 的所有字段并将返回的字段插入到 tab_2表中。

图: 查询

INSERT INTO tab_2 SELECT new_row(exp).* FROM tab_1

有关如何使用 INSERT 语句的信息，请参阅修改数据。

重要

只能对使用 .* 符号表示法的表达式求值一次。

从集合中选择

本节描述如何查询对集合类型定义的列。集合类型是一种复杂数据类型。其中每个集合值包含具有相同数据类型的一组元素。有关如何访问集合包含的个别元素的信息，请参阅处理 SELECT 语句中的集合。

下图显示 manager 表，在本节的示例中使用了该表。manager 表同时包含简单集合类型和嵌套集合类型。简单集合是一种集合类型。它不包含本身就是集合类型的任何字段。 manager 表的 direct_reports 列就是一个简单集合。嵌套集合是包含另一集合类型的集合类型。manager 表的projects 列就是一个嵌套集合。

图: manager 表

CREATE TABLE manager
(
mgr_name        VARCHAR(30),
department      VARCHAR(12),
direct_reports  SET(VARCHAR(30) NOT NULL),
projects        LIST(ROW(pro_name VARCHAR(15),
pro_members SET(VARCHAR(20) NOT NULL)
) NOT NULL)
)

对于表中的每一行，对作为集合类型的列的查询返回特定集合包含的所有元素。例如：以下查询显示对 manager 表的每一行返回 department 列中的数据和 direct_reports 列中的所有元素的查询。

图: 查询

SELECT department, direct_reports FROM manager

图: 查询结果

department      marketing
direct_reports  SET {Smith, Waters, Adams, Davis, Kurasawa}

department      engineering
ddirect_reports  SET {Joshi, Davis, Smith, Waters, Fosmire, Evans, Jones}

department      publications
direct_reports  SET {Walker, Fremont, Porat, Johnson}

department      accounting
direct_reports  SET {Baker, Freeman, Jacobs}
⋮

对集合类型查询的输出总是包括类型构造函数，它指定集合是 SET 、MULTISET 或 LIST 。例如：在此结果中，SET 构造函数位于每个集合的元素前面。花括号（{}）划分集合的元素；逗号隔开集合的个别元素。

选择嵌套集合

manager 表的 projects 列是嵌套集合（请参阅图 1）。对嵌套集合类型的查询返回特定集合包含的所有元素。下列查询显示返回 projects 列的特定行中所有元素的查询。WHERE 子句将查询限制为单个行，在其中，mgr_name 列中的值是 Sayles。

图: 查询

SELECT projects
FROM manager
WHERE mgr_name = 'Sayles'

查询结果显示 manager 表的单个行的 project 列集合。查询返回管理 Sayles 描述的那些项目的名称。对于 LIST 中的每个元素，集合包含项目名（pro_name）和指定给每个项目的成员（pro_members）的 SET 。

图: 查询结果

projects  LIST {ROW(voyager_project, SET{Simonian, Waters, Adams, Davis})}

projects  LIST {ROW(horizon_project, SET{Freeman, Jacobs, Walker, Cannan})}

projects  LIST {ROW(sapphire_project, SET{Villers, Reeves, Doyle, Strongin})}
⋮

使用 IN 关键字来搜索集合中的元素

可以在 SQL 语句的 WHERE 子句中使用 IN 关键字来确定集合是否包含某元素。例如：下列查询显示如何构造返回 mgr_name 和 department 的值的查询，其中 Adams 是 direct_reports 列中集合的一个元素。

图: 查询

SELECT mgr_name, department
FROM manager
WHERE 'Adams' IN direct_reports

图: 查询结果

mgr_name Sayles
department marketing

尽管可以使用带有 IN 关键字的 WHERE 子句来搜索简单集合中的特定集合。但是查询总是返回整个集合。例如：下列查询返回集合的所有元素，其中 Adams 是 direct_reports 列中集合的一个元素。

图: 查询

SELECT mgr_name, direct_reports
FROM manager
WHERE 'Adams' IN direct_reports

图: 查询结果

mgr_name Sayles
direct_reports SET {Smith, Waters, Adams, Davis, Kurasawa}

如上所示，对集合列的查询返回整个集合，不是集合中的特定元素。

可以在 WHERE 子句中使用 IN 关键字来只引用简单集合。不能使用 IN 关键字来引用包含本身就是集合的字段的集合。例如：不能使用 IN 关键字来引用 manager 表中的 projects 列，原因是 projects 是嵌套集合。

可以在 SELECT 语句的 WHERE 子句中组合 NOT 和 IN 关键字来搜索不包含某元素的集合。例如：下列查询显示返回 mgr_name 和 department的值的查询，其中 Adams 不是 direct_reports 列中集合的元素。

图: 查询

SELECT mgr_name, department
FROM manager
WHERE 'Adams' NOT IN direct_reports

图: 查询结果

mgr_name     Williams
department   engineering

mgr_name     Lyman
department   publications

mgr_name     Cole
department   accounting

有关如何对集合列中的元素进行技术的信息，请参阅基数函数。

选择表层次结构中的行

本节描述如何从表层次结构内的表查询行。

下列查询显示创建本节中的示例使用的类型和表层次结构的语句。

图: 创建类型和表层次结构的语句

CREATE ROW TYPE address_t
(
street   VARCHAR (20),
city     VARCHAR(20),
state    CHAR(2),
zip      VARCHAR(9)
)

CREATE ROW TYPE person_t
(
name     VARCHAR(30),
address  address_t,
soc_sec  CHAR(9)
)

CREATE ROW TYPE employee_t
(
salary       INTEGER
)
UNDER person_t

CREATE ROW TYPE sales_rep_t
(
rep_num     SERIAL8,
region_num  INTEGER
)
UNDER employee_t

CREATE TABLE person OF TYPE person_t

CREATE TABLE employee OF TYPE employee_t
UNDER person

CREATE TABLE sales_rep OF TYPE sales_rep_t
UNDER employee

下图显示上图中的行类型和表的层次关系。

图: 类型和表层次结构

不使用 ONLY 关键字选择超表的行

表层次结构允许您在单个 SQL 语句中构造其作用域是超表及其子表的查询。对超表的查询同时从超表及其子表中返回行。下列查询显示对 person表（它是表层次结构中的 root 超表）的查询。

图: 查询

SELECT * FROM person

图 8中继承自超表的那些列。对超表的查询不返回不在超表中的子表的列。该查询显示person 、employee 和 sales_rep 表中的 name 、address 和 soc_sec 列。

图: 查询结果

name        Rogers, J.
address     ROW(102 Ruby Ave, Belmont, CA, 69055)
soc_sec     454849344

name        Sallie, A.
address     ROW(134 Rose St, San Carlos, CA, 69025)
soc_sec     348441214
⋮

使用 ONLY 关键字选择超表的行

尽管超表上的 SELECT 语句同时从该超表及其子表中返回行。但是不能辨别哪些行来自超表，哪些行来自子表。要将查询的结果限制为只是超表，必须在 SELECT 语句中包含 ONLY 关键字。例如，下列查询只返回 person 表中的行。

图: 查询

SELECT * FROM ONLY(person);

图: 查询结果

name Rogers, J.
address ROW(102 Ruby Ave, Belmont, CA, 69055)
soc_sec 454849344
⋮

对超表使用别名

别名是 FROM 子句中紧跟在表名后面的词，可以在 SELECT 或 UPDATE 语句中为类型表指定别名，然后将别名本身用作表达式。如果为超表创建别名，那么该别名可表示来自该超表或继承自该超表的子表中的值。在 DB-Access 中，下列查询返回 person 、employee 和 sales_rep 表的所有实例的行值。

图: 查询

SELECT p FROM person p;

GBase 8s ESQL/C 不能识别此构造。在 GBase 8s ESQL/C 程序中，该查询返回一个错误。

总结

本章介绍了通过使用 SELECT 语句来查询关系数据库以从复杂类型中选择数据的样本语法和结果。选择行类型数据一节显示如何执行下列操作：

从类型表和列中选择行类型数据
将行类型表达式用于字段投影

从集合中选择一节显示如何执行下列操作：

集合类型上定义的查询列
查询定义为嵌套集合类型的列
在嵌套集合类型上定义的查询列

选择表层次结构中的行一节显示如何执行下列操作：

使用或不使用 ONLY 关键字来查询超表
为超表指定别名

选择行类型数据​

选择类型表的列​

选择包含行类型数据的列​

字段投影​

使用字段投影来选择嵌套字段​

选择行类型的个别字段​

使用星号表示法来访问行类型的所有字段​

从集合中选择​

选择嵌套集合​

使用 IN 关键字来搜索集合中的元素​

选择表层次结构中的行​

不使用 ONLY 关键字选择超表的行​

使用 ONLY 关键字选择超表的行​

对超表使用别名​

总结​

选择行类型数据

选择类型表的列

选择包含行类型数据的列

字段投影

使用字段投影来选择嵌套字段

选择行类型的个别字段

使用星号表示法来访问行类型的所有字段

从集合中选择

选择嵌套集合

使用 IN 关键字来搜索集合中的元素

选择表层次结构中的行

不使用 ONLY 关键字选择超表的行

使用 ONLY 关键字选择超表的行

对超表使用别名

总结